package dao.imp;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;


import dao.RevenueDAO;
import imageUtil.Page;
import model.Revenue;
import util.DBConnection;

public class RevenueDAOImp implements RevenueDAO{

	private static final String findByCount = "select count(*) from income";
	
	private static final String findByAll = "select * from income limit ?,?";
	
	private static final String findnameByID = "select department_name from department where id=?";
	
	private static final String insertIncome = "insert into income(Department_Name,Bussiness_Date,Last_mod_Date,Daily_Income) values(?,?,?,?)";
	
	private static final String updateIncome = "update income set department_name=?,Last_mod_Date=?,Daily_Income=? where id=?";
	@Override
	public int findByCount() throws Exception {
		PreparedStatement prep= null;
		Connection conn= null;
		int count= 0;
		ResultSet rst= null;
		try {
			conn= DBConnection.getConnection();		    	  
			prep= conn.prepareStatement(findByCount);
			rst= prep.executeQuery();
			if(rst.next()) {
				count=rst.getInt(1);
			}
		} finally{
			DBConnection.close(prep, conn);
		}
			return count; 
	}
	public List<Revenue> findByAll(Page page) throws Exception {
		PreparedStatement prep= null;
		Connection conn= null;
		List<Revenue> reve = new ArrayList<Revenue>();
		Revenue i= null;
		ResultSet rst= null;
		try {
			conn= DBConnection.getConnection();		    	  
			prep= conn.prepareStatement(findByAll);
			prep.setInt(1, page.getBeginIndex());
			prep.setInt(2, page.getEveryPage());
			rst= prep.executeQuery();
			while(rst.next()) {
				i= new Revenue();
				i.setId(rst.getInt("id"));
				i.setDepartmentName(rst.getString("Department_Name"));
				i.setBussinessDate(rst.getDate("Bussiness_Date"));
				i.setLastmodDate(rst.getDate("Last_mod_Date"));
				i.setDailyIncome(rst.getDouble("Daily_Income"));
				reve.add(i);
			}
		} finally{
			DBConnection.close(prep, conn);
		}
			return reve; 
	}
	@Override
	public String findDeptNameById(int id) throws Exception {
		Connection conn = null;
		PreparedStatement prep = null; 
		ResultSet rs = null;
		try{
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(findnameByID);
			prep.setInt(1, id);
			rs	= prep.executeQuery();
	        if(rs.next()) {	
				return rs.getString("department_name");
			}
		} finally {
			DBConnection.close(rs, prep, conn);
		}
		return null;
	}
	@Override
	public boolean saveIncome(Revenue revenue) {
		PreparedStatement prep = null;
		Connection conn = null;
		int i = 0;
		try{
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(insertIncome);
			prep.setString(1, revenue.getDepartmentName());
			prep.setDate(2, new java.sql.Date(revenue.getBussinessDate().getTime()));
			prep.setDate(3, new java.sql.Date( revenue.getLastmodDate().getTime()));
			prep.setDouble(4, revenue.getDailyIncome());
			i = prep.executeUpdate();
			} catch (Exception e) {
				e.printStackTrace();
			} finally {
				try {
					DBConnection.close(prep, conn);
				} catch (Exception e) {
					e.printStackTrace();
				}
			}
		if(i != 0){
			return true;
		}else {
			return false;
		}
	}
	@Override
	public boolean updateIncome(Revenue revenue) {
		PreparedStatement prep = null;
		Connection conn = null;
		int i = 0;
		try{
			conn = DBConnection.getConnection();
			prep = conn.prepareStatement(updateIncome);
			prep.setString(1, revenue.getDepartmentName());
			prep.setDate(2, new java.sql.Date(new Date().getTime()));
			prep.setDouble(3, revenue.getDailyIncome());
			prep.setInt(4, revenue.getId());
			i = prep.executeUpdate();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				DBConnection.close(prep, conn);
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
		if(i != 0){
			return true;
		}else {
			return false;
		}	
	}

	 
}
